package net.uman.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * UserDao的测试用例, 测试ORM映射及特殊的DAO操作.
 * 
 * 默认在每个测试函数后进行回滚.
 * 
 * @author calvin
 */
public class DataInserter {
	public final static int N_CATEGORY = 7;
	public final static int N_GOODS = 500000;
	public final static int N_TRADEGOODS = 500000;
	public final static int N_TRADEOBJECT = 50000000;
	public final static int N_LOGISTICS = 100;
	public final static int N_SUPPLIER = 1000;
	public final static int N_CHAINHQ = 100;
	public final static int N_BRAND = 1000;
	public final static int N_STORE = 50000;
	public final static int N_LCTOBJECT = 500000;
	public final static int N_LTCOVER = 500000;
	public final static int N_LTSTORE = 100000;
	public final static int N_SGOODSCATALOG = 500000;
	public final static int N_CGOODSCATALOG = 500000;
	public final static int N_CSTORESTOCK = 50000000;
	public final static int N_CHAINORDERPRICE = 1000;
	public final static int N_RETAILPRICE = 1000;
	public final static int N_STOREORDERPRICE = 1000;
	public final static int N_DELIVERYFEE = 500000;
	
	public void createData() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        
        String url = "jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = trac-scan)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = coc) ) )";
        String user = "coctest";
        String pwd = "coctest";
        Connection conn = DriverManager.getConnection(url, user, pwd);
        conn.setAutoCommit(false);
        
        List<IDataProvider> providers = new ArrayList<IDataProvider>();
//        providers.add(createShopOrder());
//        providers.add(createShopOrderStatus());
        
        providers.add(createBrands());
        providers.add(createCategories());
        providers.add(createGoods());
        providers.add(createTradeGoods());
        providers.add(createLogistics());
        providers.add(createChainhq());
        providers.add(createSuppliers());
        providers.add(createStores());
        providers.add(createCGoodsCatalog());
        providers.add(createStoreOrderPrice());
        providers.add(createChainOrderPrice());
        providers.add(createTradeObject());
        providers.add(createRetailPrice());
        providers.add(createSGoodsCatalog());
        providers.add(createCStoreStock());
        providers.add(createLTCover());
        providers.add(createLCTObject());
        providers.add(createLTStore());
        providers.add(createDeliveryFee());
        
        for (IDataProvider provider : providers) {
        	System.out.println("Table: "+provider.getName());

        	PreparedStatement st = conn.prepareStatement(provider.getSQL());
        	
        	for (int i = 1 ; ; i++) {
        		if (!provider.setData(st)) {
	                st.executeBatch();
	                conn.commit();
	                
	                System.out.println("Inserted "+i);
	                break;
	        	}

        		st.addBatch();
        		
        		if (i % 1000 == 0) {
	                st.executeBatch();
	                conn.commit();        			
        		}
        		
        		if (i % 100000 == 0) {
        			System.out.println("Inserted "+i);
        		}
        	}
        	
        	st.close();
        }
        
        conn.close();
	}
	
	private IDataProvider createShopOrder() throws Exception {
		return new IDataProvider() {
			int i = 300000;
			public String getSQL() {
				return "insert into shop_order(shop_order_id,trade_goods_name,b2b_code) values(?,?,?)";
			}
			
			public String getName() {
				return "ShopOrder";
			}

			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > 600000) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setString(2, "G"+i);
				statement.setString(3, "B"+i);
				return true;
			}
		};
	}
	
	private IDataProvider createShopOrderStatus() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into shop_order_status(shop_order_status_id,shop_order_id,note) values(?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > 300000) {
					return false;
				}
				
				statement.setLong(1, i+1000000);
				statement.setLong(2, i);
				statement.setString(3, "N"+i);
				return true;
			}
			
			public String getName() {
				return "ShopOrderStatus";
			}
		};
	}

	private IDataProvider createStores() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into chain_store(chain_store_id,chain_headquarters_id,CONNECTOR,name) values(?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_STORE) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i % N_CHAINHQ + 1);
				statement.setString(3, "张"+i);
				statement.setString(4, "门店"+i);
				return true;				
			}
			
			public String getName() {
				return "ChainStore";
			}
		};
	}
	
	private IDataProvider createChainhq() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into chain_headquarters(chain_headquarters_id,name,category_id)  values (?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_CHAINHQ) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setString(2, "物美"+i);
				statement.setLong(3, i % N_CATEGORY + 1);
				return true;				
			}
			
			public String getName() {
				return "ChainHeadquarters";
			}
		};
	}
	
	private IDataProvider createLogistics() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into logistics(logistics_id,name,category_id) values (?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_LOGISTICS) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setString(2, "配送"+i);
				statement.setLong(3, i % N_CATEGORY + 1);
				return true;				
			}
			
			public String getName() {
				return "Logistics";
			}
		};
	}
	
	private IDataProvider createSuppliers() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into supplier(supplier_id,name,category_id) values(?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_SUPPLIER) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setString(2, "供货商"+i);
				statement.setLong(3, i % N_CATEGORY + 1);
				return true;				
			}
			
			public String getName() {
				return "Supplier";
			}
		};
	}
	
	private IDataProvider createTradeObject() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into trade_object(trade_object_id,trade_goods_id,supplier_id,chain_headquarters_id,`c_goods_catalog_id`,`chain_order_price_id`,`goods_name`,`b2b_code`,`status`,`sale_type`) "+
					"values (?,?,?,?,?,?,?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_TRADEOBJECT) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i % N_TRADEGOODS + 1);
				statement.setLong(3, i % N_SUPPLIER + 1);
				statement.setLong(4, i % N_CHAINHQ + 1);
				statement.setLong(5, i % N_CGOODSCATALOG + 1);
				statement.setLong(6, i % N_CHAINORDERPRICE + 1);
				statement.setString(7, "NOKIA N"+i);
				statement.setString(8, "B"+i);
				statement.setInt(9, i % 2);
				statement.setInt(10, i % 3);
				return true;				
			}
			
			public String getName() {
				return "TradeObject";
			}
		};
	}
	
	private IDataProvider createTradeGoods() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into trade_goods(trade_goods_id,goods_id,b2b_code,name,status,`category_id`) values(?,?,?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_TRADEGOODS) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i);
				statement.setString(3, "B"+i);
				statement.setString(4, "NOKIA N"+i);
				statement.setInt(5, i % 5);
				statement.setLong(6, i % N_CATEGORY +1);
				return true;				
			}
			
			public String getName() {
				return "TradeGoods";
			}
		};
	}	
	
	private IDataProvider createGoods() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into goods (`goods_id`,`deleted`,`market_price`,`goods_name`,`status`,`brand_id`,`category_id`) "+
					"values(?,false,1000,?,0,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_GOODS) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setString(2, "NOKIA N"+i);
				statement.setLong(3, i % N_BRAND + 1);
				statement.setLong(4, i % N_CATEGORY + 1);
				return true;				
			}
			
			public String getName() {
				return "Goods";
			}
		};
	}

	private IDataProvider createBrands() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into `brand` (`brand_id`,`name`,`logo`,`description`,`site_url`,`sort_order`,`is_show`  ) values (?,?,'images/100727/nokia.gif','官方咨询电话：4008800123售后网点：http://www.nokia.com.cn/A4630632','http://www.nokia.com.cn',1,true)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_BRAND) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setString(2, "诺基亚"+i);
				return true;				
			}
			
			public String getName() {
				return "Brand";
			}
		};
	}

	private IDataProvider createCategories() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into category(`category_id`,`cate_order`,`description`,`filter_attr`,`grade`,`is_show`,`keywords`,`measure_unit`,`name`,`show_in_nav`,`style`,`template_file`,`parent_id`)  values(?, 1, '','',true,true,'','',?,true,'','',null)";
			}
				
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_CATEGORY) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setString(2, "C"+i);
				return true;
			}
			
			public String getName() {
				return "Category";
			}
		};
	}
	
	private IDataProvider createSGoodsCatalog() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into s_goods_catalog(s_goods_catalog_id,s_goods_code,s_goods_name,trade_goods_id,supplier_id) values(?,?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_SGOODSCATALOG) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setString(2, "TB"+i);
				statement.setString(3, "笔记本"+i);
				statement.setLong(4, i % N_TRADEGOODS + 1);
				statement.setLong(5, i % N_SUPPLIER + 1);
				return true;				
			}
			
			public String getName() {
				return "SGoodsCatalog";
			}
		};
	}

	private IDataProvider createLCTObject() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into l_c_t_object(l_c_t_object_id,`supplier_id`,`chain_headquarters_id`,`logistics_id`,`l_t_cover_id`,`delivery_object_type`,`delivery_object_id`) "+
					"values(?,?,?,?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_LCTOBJECT) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i % N_SUPPLIER + 1);
				statement.setLong(3, i % N_CHAINHQ + 1);
				statement.setLong(4, i % N_LOGISTICS + 1);
				statement.setLong(5, i % N_LTCOVER + 1);
				statement.setInt(6, i % 2);
				statement.setLong(7, i%2==0 ? (i % N_CHAINHQ + 1) : (i % N_TRADEOBJECT + 1));
				return true;				
			}
			
			public String getName() {
				return "LCTObject";
			}
		};
	}

	private IDataProvider createLTStore() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into l_t_store(l_t_store_id,l_t_cover_id,chain_store_id,logistics_id) values(?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_LTSTORE) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i % N_LTCOVER + 1);
				statement.setLong(3, i % N_STORE + 1);
				statement.setLong(4, i % N_LOGISTICS + 1);
				return true;				
			}
			
			public String getName() {
				return "LTStore";
			}
		};
	}

	private IDataProvider createCGoodsCatalog() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into `c_goods_catalog` (`c_goods_catalog_id`, `status`, `chain_headquarters_id`, `trade_goods_id`, `b2b_code`) "+
					"values (?,1,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_CGOODSCATALOG) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i % N_CHAINHQ + 1);
				statement.setLong(3, i % N_TRADEGOODS + 1);
				statement.setString(4, "B"+i);
				return true;				
			}
			
			public String getName() {
				return "CGoodsCatalog";
			}
		};
	}
	
	private IDataProvider createCStoreStock() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into `c_store_stock` (`c_store_stock_id`, `chain_store_id`, `c_goods_catalog_id`, `advice_stock`, `current_stock`) "+
					"values(?,?,?,?,3)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_CSTORESTOCK) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i % N_STORE + 1);
				statement.setLong(3, i % N_CGOODSCATALOG + 1);
				statement.setInt(4, i % 30);
				return true;				
			}
			
			public String getName() {
				return "CStoreStock";
			}
		};
	}

	private IDataProvider createLTCover() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into `l_t_cover` (`l_t_cover_id`,`logistics_id`,`chain_headquarters_id`,`cover_type`,`owner_type`,`supplier_id`) "+
					"values(?,?,?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_LTCOVER) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i % N_LOGISTICS + 1);
				statement.setLong(3, i % N_CHAINHQ + 1);
				statement.setLong(4, i % 3);
				statement.setLong(5, i % 2);
				statement.setLong(6, i % N_SUPPLIER + 1);
				return true;				
			}
			
			public String getName() {
				return "LTCover";
			}
		};
	}
	
	private IDataProvider createDeliveryFee() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into `delivery_fee` (`delivery_fee_id`,`fee_type_id`,`lct_id`,`fee_type`,`return_delivery_fee`,`order_delivery_fee`) "+
					"values(?,?,?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_DELIVERYFEE) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setLong(2, i%2==0 ? (i%N_LCTOBJECT + 1) : (i%N_TRADEOBJECT + 1));
				statement.setLong(3, i % N_LCTOBJECT + 1);
				statement.setInt(4, i % 2);
				statement.setInt(5, i % 3);
				statement.setInt(6, i % 2);
				return true;				
			}
			
			public String getName() {
				return "DeliveryFee";
			}
		};
	}

	private IDataProvider createChainOrderPrice() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into `chain_order_price` (`chain_order_price_id`,`return_price`,`purchase_price`) values(?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_CHAINORDERPRICE) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setInt(2, i % 1000);
				statement.setInt(3, i % 800);
				return true;				
			}
			
			public String getName() {
				return "ChainOrderPrice";
			}
		};
	}

	private IDataProvider createRetailPrice() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into `retail_price` (`retail_price_id`, `retail_price_type`, `source_id`, `c_goods_catalog_id`, `retail_price`) values " +
						"(?,?,?,?,?)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_RETAILPRICE) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setInt(2, i % 2);
				statement.setLong(3, i % 2 == 0 ? (i % N_CHAINHQ + 1) : (i % N_STORE + 1));
				statement.setLong(4, i % N_CGOODSCATALOG + 1);
				statement.setInt(5, i % 1000);
				return true;				
			}
			
			public String getName() {
				return "RetailPrice";
			}
		};
	}

	private IDataProvider createStoreOrderPrice() throws Exception {
		return new IDataProvider() {
			int i = 0;
			public String getSQL() {
				return "insert into `store_order_price` (`store_order_price_id`, `store_order_price_type`, `source_id`, `order_price`, `return_price`, `c_goods_catalog_id`, `profit`, `member_profit`) "+
					"values (?,?,?,?,?,?,2,1)";
			}
			
			public boolean setData(PreparedStatement statement) throws SQLException {
				i++;
				
				if ( i > N_STOREORDERPRICE) {
					return false;
				}
				
				statement.setLong(1, i);
				statement.setInt(2, i % 2);
				statement.setLong(3, i % 2 == 0 ? (i % N_CHAINHQ + 1) : (i % N_STORE + 1));
				statement.setInt(4, i % 1000);
				statement.setInt(5, i % 800);
				statement.setLong(6, i % N_CGOODSCATALOG + 1);
				return true;				
			}
			
			public String getName() {
				return "StoreOrderPrice";
			}
		};
	}

	interface IDataProvider {
		String getName();
		String getSQL();
		boolean setData(PreparedStatement statement) throws SQLException;
	}
	
	public static void main(String[] args) {
		try {
			new DataInserter().createData();
		} catch (Exception e) {
			e.printStackTrace();
		}
//		new DbInitTest().clearData();
		System.out.println("done");
	}
}